
package com.xspace.android.imcommon.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.text.TextUtils;

import com.xspace.android.imcommon.entity.PushMsg;
import com.xspace.android.xafcommon.util.LogUtils;
import com.xspace.android.xafcommon.util.StringUtils;

import java.util.ArrayList;
import java.util.List;


public class PushDb extends BaseDb {

	public PushDb(Context context) {
		super(context);
	}

	/**
	 * _id
	 */
	public static final String _ID = "_id";
	/**
	 * 推送平台号
	 */
	public static final String COLOUMN_PLATFORM = "platform";
	/**
	 * 实际推送用户ID
	 */
	public static final String COLOUMN_PUSH_USER_ID = "push_user_ID";
	/**
	 * 实际推送用户名称
	 */
	public static final String COLOUMN_PUSH_USER_NAME = "push_user_name";

	/**
	 * 标题
	 */
	public static final String COLOUMN_TITLE = "title";
	/**
	 * 类型
	 */
	public static final String COLOUMN_TYPE = "type";
	/**
	 * 内容类型
	 */
	public static final String COLOUMN_MSG_BODY_TYPE = "msg_body_type";
	/**
	 * 内容类型图片
	 */
	public static final String COLOUMN_MSG_BODY_TYPE_LOGO = "msg_body_type_logo";
	/**
	 * 内容
	 */
	public static final String COLOUMN_CONTENT = "content";
	/**
	 * 推送时间
	 */
	public static final String COLOUMN_PUSH_TIME = "push_time";
	/**
	 * 插入时间
	 */
	public static final String COLOUMN_INSERT_TIME = "insert_time";
	/**
	 * 推送消息的代理
	 */
	public static final String COLOUMN_PUSH_AGENT = "push_agent";
	
	/**
	 * 推送消息的代理
	 */
	public static final String COLOUMN_DEPT_TYPE_NAME = "dept_type_name";
	/**
	 * 	推送是否读取
	 */
	public static final String COLOUMN_PUSH_IS_READ = "isread";
	/**
	 * 推送消息id
	 */
	public static final String COLOUMN_MSGID = "msgid";

	/**
	 * 推送消息所属人
	 */
	public static final String COLOUMN_OWNER = "owner";
	public static final String TABLE_NAME = "PUSH_MSG";
	public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER primary key, " 
	        + COLOUMN_PLATFORM + " TEXT, " 
			+ COLOUMN_PUSH_USER_ID+ " TEXT, " 
			+ COLOUMN_PUSH_USER_NAME + " TEXT, " 
			+ COLOUMN_TITLE + " TEXT, " 
			+ COLOUMN_MSG_BODY_TYPE + " TEXT, " 
			+ COLOUMN_MSG_BODY_TYPE_LOGO + " TEXT, "
			+ COLOUMN_CONTENT + " TEXT, " 
			+ COLOUMN_PUSH_TIME + " TEXT, " 
			+ "" + COLOUMN_INSERT_TIME + " TEXT, " 
			+ COLOUMN_PUSH_AGENT + " TEXT, " 
			+ COLOUMN_MSGID + " TEXT, "
			+ COLOUMN_TYPE + " TEXT, " 
			+ COLOUMN_DEPT_TYPE_NAME + " TEXT, "
			+ COLOUMN_PUSH_IS_READ + " INTEGER, "
			+ COLOUMN_OWNER + " TEXT)";

	@Override
	String getTableName() {
		return TABLE_NAME;
	}

	/**
	 * 修改未读消息数量为已读
	 * @param pushMsg
	 * @param owner
	 */
	public void UpdateUnReadCount(PushMsg pushMsg, String owner) {
		if(pushMsg==null) return;
		//Log.i("pushdb", "updateunreadcount pushMsg getmPlatform:"+pushMsg.getmPlatform()+" owner:"+owner);
		ContentValues values = new ContentValues();
		values.put(COLOUMN_PUSH_IS_READ, 0);
		int result=db.update(TABLE_NAME, values, COLOUMN_PUSH_USER_ID+ "=? and " + COLOUMN_OWNER + "=?",
				new String[] { pushMsg.getmPushUserID(), owner });
		LogUtils.i("updateunreadcount result: "+result);
	}
	/**
	 * 保存消息数据
	 * 
	 * @return
	 */
	public int insertOrUpdateMsg(PushMsg pushMsgBean, String owner) {
		ContentValues values = new ContentValues();
		values.put(COLOUMN_CONTENT, pushMsgBean.getmContent());
		values.put(COLOUMN_INSERT_TIME, pushMsgBean.getmInsertTime());
		values.put(COLOUMN_MSGID, pushMsgBean.getmMsgid());
		values.put(COLOUMN_OWNER, owner);
		values.put(COLOUMN_PLATFORM, pushMsgBean.getmPlatform());
		values.put(COLOUMN_PUSH_AGENT, pushMsgBean.getmPushAgent());
		values.put(COLOUMN_PUSH_TIME, pushMsgBean.getmPushTime());
		values.put(COLOUMN_PUSH_USER_ID, pushMsgBean.getmPushUserID());
		values.put(COLOUMN_PUSH_USER_NAME, pushMsgBean.getmPushUserName());
		values.put(COLOUMN_DEPT_TYPE_NAME, pushMsgBean.getChannelName());
		System.out.println("------------------pushMsgBean.getChannelName():"+pushMsgBean.getChannelName());
		String bodyType = pushMsgBean.getmMsgBodyType();
		if (StringUtils.isNull(bodyType)) {
			bodyType = "系统消息";
		}
		values.put(COLOUMN_MSG_BODY_TYPE, bodyType);
		values.put(COLOUMN_MSG_BODY_TYPE_LOGO, pushMsgBean.getmMsgBodyTypeLogo());
		values.put(COLOUMN_TITLE, pushMsgBean.getmTitle());
		values.put(COLOUMN_TYPE, pushMsgBean.getmType());
		values.put(COLOUMN_PUSH_IS_READ, pushMsgBean.getIsRead());
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_MSGID + " = '" + pushMsgBean.getmMsgid() + "' and " + COLOUMN_OWNER + " = '" + owner + "'";
		Cursor cursor = null;
		try {
			long rowId = -1;
			checkDb();
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				rowId = db.update(TABLE_NAME, values, COLOUMN_MSGID + "=? and " + COLOUMN_OWNER + "=?", new String[] { pushMsgBean.getmMsgid(), owner });
			} else {
				rowId = db.insert(TABLE_NAME, null, values);
			}
			return (int) rowId;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			closeDbAndCursor(cursor);
		}
	}

	/**
	 * 
	 * @Description: 分组查询每组最新推送消息列表
	 * @param
	 * @return
	 * @throws
	 */
	public ArrayList<PushMsg> queryLatestPushMsgs(String owner,String channelName) {
		ArrayList<PushMsg> pushMsgs = new ArrayList<PushMsg>();
		Cursor cursor = null;
		try {
			checkDb();
			   if(StringUtils.isNull(channelName)){
		        	cursor = db.query(TABLE_NAME, new String[] { _ID, COLOUMN_PLATFORM, COLOUMN_PUSH_USER_ID, COLOUMN_PUSH_USER_NAME, COLOUMN_TITLE, COLOUMN_MSG_BODY_TYPE,
					COLOUMN_MSG_BODY_TYPE_LOGO, COLOUMN_CONTENT, COLOUMN_PUSH_TIME, COLOUMN_INSERT_TIME, COLOUMN_PUSH_AGENT, COLOUMN_MSGID, COLOUMN_OWNER,COLOUMN_DEPT_TYPE_NAME,
					"max(" + COLOUMN_PUSH_TIME + ")" ,"sum("+COLOUMN_PUSH_IS_READ+")"}, COLOUMN_OWNER + "='" + owner +"'", null,COLOUMN_PUSH_USER_ID, null, COLOUMN_PUSH_TIME + " desc");
			   }else{
				   cursor = db.query(TABLE_NAME, new String[] { _ID, COLOUMN_PLATFORM, COLOUMN_PUSH_USER_ID, COLOUMN_PUSH_USER_NAME, COLOUMN_TITLE, COLOUMN_MSG_BODY_TYPE,
							COLOUMN_MSG_BODY_TYPE_LOGO, COLOUMN_CONTENT, COLOUMN_PUSH_TIME, COLOUMN_INSERT_TIME, COLOUMN_PUSH_AGENT, COLOUMN_MSGID, COLOUMN_OWNER,COLOUMN_DEPT_TYPE_NAME,
							"max(" + COLOUMN_PUSH_TIME + ")","sum("+COLOUMN_PUSH_IS_READ+")" }, COLOUMN_OWNER + "='" + owner + "' and " +COLOUMN_DEPT_TYPE_NAME+"='"+channelName+"'", null, COLOUMN_PLATFORM + "," 
							+ COLOUMN_PUSH_USER_ID, null, COLOUMN_PUSH_TIME + " desc");
					  
			   }
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						PushMsg pushMsg = new PushMsg(cursor.getInt(cursor.getColumnIndex(_ID)), cursor.getString(cursor.getColumnIndex(COLOUMN_PLATFORM)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_USER_ID)), cursor.getString(cursor.getColumnIndex(COLOUMN_PUSH_USER_NAME)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_TITLE)), cursor.getString(cursor.getColumnIndex(COLOUMN_MSG_BODY_TYPE)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_MSG_BODY_TYPE_LOGO)), cursor.getString(cursor.getColumnIndex(COLOUMN_CONTENT)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_TIME)), cursor.getString(cursor.getColumnIndex(COLOUMN_INSERT_TIME)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_AGENT)), cursor.getString(cursor.getColumnIndex(COLOUMN_MSGID)),
								cursor.getString(cursor.getColumnIndex(COLOUMN_OWNER)),cursor.getString(cursor.getColumnIndex(COLOUMN_DEPT_TYPE_NAME)));
						//Log.i("pushdb", "getString(14): "+cursor.getString(14));
						int unreadcount=cursor.getInt(15);
						//Log.i("pushdb", "unreadcount: "+unreadcount+" channelname:"+pushMsg.getChannelName());
						pushMsg.setIsRead(unreadcount);
						pushMsgs.add(pushMsg);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		return pushMsgs;
	}

	/**
	 * 
	 * @Description: 查询每组最新推送消息列表
	 * @param pushUserID
	 * @param platform
	 * @param owner
	 * @return
	 * @throws
	 */
	public ArrayList<PushMsg> queryPushMsgs(String pushUserID, String platform, String owner, String beginTime, String endTime,String key) {
		ArrayList<PushMsg> pushMsgs = new ArrayList<PushMsg>();
		String where;
		Cursor cursor=null;
		String[] selectionArgs=null;
		if (!StringUtils.isNull(beginTime) && !StringUtils.isNull(endTime)) {
			where = COLOUMN_OWNER + "='" + owner + "' and " + COLOUMN_PLATFORM + "='" + platform + "' and " + COLOUMN_PUSH_USER_ID + " ='" + pushUserID + "' and"
					+ " push_time < +'" + endTime + "'" + " and push_time >'" + beginTime + "'";
		} else {
			where = COLOUMN_OWNER + "='" + owner + "' and " + COLOUMN_PLATFORM + "='" + platform + "' and " + COLOUMN_PUSH_USER_ID + " ='" + pushUserID + "'";
		}
		
		if(!TextUtils.isEmpty(key)){
			where=where+" and "+ COLOUMN_CONTENT+" like ?";
			selectionArgs=new String[]{"%"+ key +"%"};
		}
		try {
			checkDb();
			cursor = db.query(TABLE_NAME, new String[] { _ID, COLOUMN_PLATFORM, COLOUMN_PUSH_USER_ID, COLOUMN_PUSH_USER_NAME, COLOUMN_TITLE, COLOUMN_MSG_BODY_TYPE,
					COLOUMN_MSG_BODY_TYPE_LOGO, COLOUMN_CONTENT, COLOUMN_PUSH_TIME, COLOUMN_INSERT_TIME, COLOUMN_PUSH_AGENT, COLOUMN_MSGID, COLOUMN_OWNER, COLOUMN_DEPT_TYPE_NAME,COLOUMN_TYPE }, where,
					selectionArgs, null, null, COLOUMN_PUSH_TIME + " desc");
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						PushMsg pushMsg = new PushMsg(cursor.getInt(cursor.getColumnIndex(_ID)), cursor.getString(cursor.getColumnIndex(COLOUMN_PLATFORM)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_USER_ID)), cursor.getString(cursor.getColumnIndex(COLOUMN_PUSH_USER_NAME)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_TITLE)), cursor.getString(cursor.getColumnIndex(COLOUMN_MSG_BODY_TYPE)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_MSG_BODY_TYPE_LOGO)), cursor.getString(cursor.getColumnIndex(COLOUMN_CONTENT)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_TIME)), cursor.getString(cursor.getColumnIndex(COLOUMN_INSERT_TIME)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_AGENT)), cursor.getString(cursor.getColumnIndex(COLOUMN_MSGID)),
								cursor.getString(cursor.getColumnIndex(COLOUMN_TYPE)), cursor.getString(cursor.getColumnIndex(COLOUMN_DEPT_TYPE_NAME)));
						pushMsgs.add(pushMsg);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		return pushMsgs;
	}
	/**
	 * 
	 * @Description: 查询每组最新推送消息列表
	 * @param pushUserID
	 * @param platform
	 * @param owner
	 * @return
	 * @throws
	 */
	public ArrayList<PushMsg> queryPushAllMsgs(String owner, String channelName) {
		ArrayList<PushMsg> pushMsgs = new ArrayList<PushMsg>();
		String where;
		Cursor cursor=null;
			where = COLOUMN_OWNER + "='" + owner + "' and " + COLOUMN_DEPT_TYPE_NAME + "='" + channelName + "'";
		try {
			checkDb();
				cursor = db.query(TABLE_NAME, new String[] { _ID, COLOUMN_PLATFORM, COLOUMN_PUSH_USER_ID, COLOUMN_PUSH_USER_NAME, COLOUMN_TITLE, COLOUMN_MSG_BODY_TYPE,
						COLOUMN_MSG_BODY_TYPE_LOGO, COLOUMN_CONTENT, COLOUMN_PUSH_TIME, COLOUMN_INSERT_TIME, COLOUMN_PUSH_AGENT, COLOUMN_MSGID, COLOUMN_OWNER, COLOUMN_DEPT_TYPE_NAME,COLOUMN_TYPE }, where,
						null, null, null, COLOUMN_PUSH_TIME + " desc");
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						PushMsg pushMsg = new PushMsg(cursor.getInt(cursor.getColumnIndex(_ID)), cursor.getString(cursor.getColumnIndex(COLOUMN_PLATFORM)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_USER_ID)), cursor.getString(cursor.getColumnIndex(COLOUMN_PUSH_USER_NAME)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_TITLE)), cursor.getString(cursor.getColumnIndex(COLOUMN_MSG_BODY_TYPE)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_MSG_BODY_TYPE_LOGO)), cursor.getString(cursor.getColumnIndex(COLOUMN_CONTENT)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_TIME)), cursor.getString(cursor.getColumnIndex(COLOUMN_INSERT_TIME)), cursor.getString(cursor
								.getColumnIndex(COLOUMN_PUSH_AGENT)), cursor.getString(cursor.getColumnIndex(COLOUMN_MSGID)),
								cursor.getString(cursor.getColumnIndex(COLOUMN_OWNER)), cursor.getString(cursor.getColumnIndex(COLOUMN_DEPT_TYPE_NAME)));
						pushMsgs.add(pushMsg);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		return pushMsgs;
	}
	/**
	 * 
	 * @Description: 获取平台推送信息类型
	 * @param @return
	 * @return
	 * @throws
	 */
	public List<String> getMsgBodyTypeArr() {
		ArrayList<String> msgBodyTypeArr = new ArrayList<String>();
		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.query(TABLE_NAME,  new String[] { COLOUMN_MSG_BODY_TYPE }, null, null, COLOUMN_MSG_BODY_TYPE,
					null, COLOUMN_INSERT_TIME + " desc");

			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						msgBodyTypeArr.add(cursor.getString(cursor.getColumnIndex(COLOUMN_MSG_BODY_TYPE)));
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		return msgBodyTypeArr;
	}

	public boolean deleteSinglePushMsg(int _id) {
		try {
			checkDb();
			String whereClause = String.format(" %s = ? ", _ID);
			String[] whereArgs = new String[] { "" + _id };
			int res = db.delete(TABLE_NAME, whereClause, whereArgs);
			return res > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * 
	 * @Description: 删除当前用户下的某推送用户id的消息
	 * @param @param platform
	 * @param @param pushUserID
	 * @param @param owner
	 * @return
	 * @throws
	 */
	public boolean deletePushMsg(String platform, String pushUserID, String owner) {
		try {
			checkDb();
			String whereClause=COLOUMN_OWNER+"=? and "+COLOUMN_PLATFORM+"=? and "+COLOUMN_PUSH_USER_ID+"=?";
			String[] whereArgs = new String[] {owner,platform,pushUserID};
			int res = db.delete(TABLE_NAME, whereClause, whereArgs);
			return res > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}


}
